
package com.dev.su.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import com.dev.su.bean.Bean;
import com.dev.su.bean.LinkBean;

public class DbConnector {

    public Connection getSqliteConnection() {
        Connection c = null;
        try {
            Class.forName("org.sqlite.JDBC");
            String path = "file:F:/Java/i/workspace/Sipder/db/sipder.db";
            c = DriverManager.getConnection("jdbc:sqlite:" + path);
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        return c;
    }

    public void createTable() {
        try {
            Connection connection = getSqliteConnection();
            if (connection != null) {
                String table = "CREATE table IF NOT EXISTS  videos ( "
                        + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                        + "source_url TEXT,"
                        + "cdn_url TEXT,"
                        + "got_time TEXT,"
                        + "url_md5 TEXT UNIQUE);";

                String mainTable = "CREATE table IF NOT EXISTS  main ( "
                        + "id INTEGER PRIMARY KEY AUTOINCREMENT,"
                        + "source_url TEXT,"
                        + "got_time TEXT,"
                        + "sipder_tag INTEGER,"
                        + "url_md5 TEXT UNIQUE);";

                Statement statement = connection.createStatement();
                statement.execute(table);
                statement.execute(mainTable);
                statement.close();
                connection.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    public void insertImgBean(Bean bean) {
        try {
            Connection sqliteConnection = getSqliteConnection();
            Statement createStatement = sqliteConnection.createStatement();
            String sql = "insert into videos values (?,?,?,?,?)";
            PreparedStatement prepareStatement = sqliteConnection.prepareStatement(sql,
                    Statement.RETURN_GENERATED_KEYS);
            prepareStatement.setString(2, bean.getmSrcUrl());
            prepareStatement.setString(3, bean.getmDesUrl());
            prepareStatement.setLong(4, bean.getTime());
            prepareStatement.setString(5, bean.getmMd5());
            prepareStatement.executeUpdate();
            System.out.println(sql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            // e.printStackTrace();
        }
    }

    public void insertLinkBean(LinkBean bean) {
        try {
            Connection sqliteConnection = getSqliteConnection();
            Statement createStatement = sqliteConnection.createStatement();
            String sql = "insert into main values (?,?,?,?,?)";
            PreparedStatement prepareStatement = sqliteConnection.prepareStatement(sql,
                    Statement.RETURN_GENERATED_KEYS);
            prepareStatement.setString(2, bean.getmSourceUrl());
            prepareStatement.setInt(4, bean.getmTag());
            prepareStatement.setLong(3, bean.getmTime());
            prepareStatement.setString(5, bean.getmMd5());
            prepareStatement.executeUpdate();
            System.out.println(sql + "------------------------------");
        } catch (SQLException e) {
            e.printStackTrace();

        }
    }

}
